package org.scboy.coobi.db.context;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlWriterConfig;
import org.apache.calcite.sql.SqlWriterConfig.LineFolding;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.impl.SqlParserImpl;
import org.apache.calcite.sql.pretty.SqlPrettyWriter;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.scboy.coobi.db.DBStyle;
import org.scboy.coobi.db.mapping.DataTypeMapping;
import org.scboy.coobi.entity.SqlConvertSchame;
import org.scboy.utils.annotations.coobi.NoLength;
import org.scboy.utils.entitys.coobi.DbColumn;
import org.scboy.utils.entitys.coobi.DbIndex;
import org.scboy.utils.entitys.coobi.DbTable;
import org.scboy.utils.entitys.coobi.DbTypeParams;

import cn.hutool.core.util.StrUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;


/**
 * 数据库操作公共方法实现类
 */
public abstract class DbStyleContext implements DBStyle {


	Log log=LogFactory.get();

	@Override
	public abstract String getDb();

	/**
	 * Jdbc前缀，用于判断数据库类型
	 * @return
	 */
	@Override
	public abstract String getJdbc();
	
	public abstract SqlConvertSchame getSchame();
	
	public abstract String getRString();
	
	public abstract String getLString();
	
	@Override
	public String getFormatName(String name) {
		return getLString()+name+getRString();
	}
	
	@Override
	public String sqlFormat(String sql) {

		SqlConvertSchame schame=getSchame();
		//验证SQL语法
		SqlParser.Config config=SqlParser.config()
				.withParserFactory(SqlParserImpl.FACTORY)
				.withUnquotedCasing(Casing.TO_UPPER)
				.withQuotedCasing(Casing.TO_UPPER)
				.withCaseSensitive(false)
				.withConformance(SqlConformanceEnum.MYSQL_5)
				.withLex(Lex.MYSQL);
		//创建SQL解析器
		SqlParser sqlParser=SqlParser.create(sql,config);
		//执行SQL解析
		SqlNode sqlNode;
		try {
			sqlNode = sqlParser.parseStmt();
		} catch (SqlParseException e) {
			throw new RuntimeException(e);
		} 
		
		//根据链接数据库来输出SQL语句
		SqlWriterConfig writerConfig=SqlPrettyWriter.config()
				.withAlwaysUseParentheses(true)
				.withUpdateSetListNewline(true)
				.withQuoteAllIdentifiers(true)
				.withFromFolding(LineFolding.TALL)
				.withIndentation(0)
				.withKeywordsLowerCase(false)
				.withDialect(schame.output);
		
		String exeSql=sqlNode.toSqlString(c-> writerConfig).toString();
		return exeSql;
	}
	
	protected String dbName="";
	
	@Override
	public void setDbName(String dbName) {
		this.dbName=dbName.toUpperCase(Locale.ENGLISH);
	}
	
	@Override
	public abstract DataTypeMapping getDataTypeMapping();

	/**
	 * 获取数据库类型
	 * @return
	 */
	public DbTypeParams getDBType(String type) {
		DataTypeMapping mapping=getDataTypeMapping();
		try {
			Method method=mapping.getClass().getMethod(type.toUpperCase(Locale.ENGLISH));
			Method parentMethod=DataTypeMapping.class.getMethod(type.toUpperCase(Locale.ENGLISH));
			NoLength noLength=parentMethod.getDeclaredAnnotation(NoLength.class);
			String dbType=(String)method.invoke(mapping);
			Map<String, Boolean> returnValue=new HashMap<String, Boolean>();
			returnValue.put(dbType, noLength==null?false:true);
			return new DbTypeParams(dbType,noLength!=null ? true : false);
		} catch (NoSuchMethodException e) {
			throw new RuntimeException("无法找到"+type.toUpperCase(Locale.ENGLISH)+"方法。",e);
		} catch (SecurityException e) {
			throw new RuntimeException("无法解析列，"+type.toUpperCase(Locale.ENGLISH)+"调用失败。",e);
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			throw new RuntimeException("无法解析列，"+type.toUpperCase(Locale.ENGLISH)+"调用失败。",e);
		}
	}
	
	@Override
	public String getCreateOrModifyColumnSql(DbColumn column) {
		StringBuilder builder=new StringBuilder();
		builder.append(this.getFormatName(column.getName()));
		builder.append(" ");
		DbTypeParams returnValue=this.getDBType(column.getDbType());
		//获取数据类型
		builder.append(returnValue.getDbType());
		if(!returnValue.isNoLength() && column.getLength()>-1){
			builder.append("(");
			builder.append(column.getLength());
			builder.append(")");
		}
		builder.append(" ");
		
		//是否不为空。
		if(column.isNotNull()){
			builder.append(" NOT NULL ");
		}
		
		//是否有列注释。
		if(StrUtil.isNotEmpty(column.getComment())) {
			builder.append(" COMMENT '");
			builder.append(column.getComment());
			builder.append("'");
		}
		return builder.toString();
	}

	@Override
	public abstract String getAddColumnCommentSql(String name, DbColumn column);

	@Override
	public abstract String getAddTableCommentSql(String name, String comment);

	@Override
	public String getExistsTableSql(String tabName) {
		return getExistsColumnSql(tabName,null);
	}

	@Override
	public String getExistsColumnSql(String tabName, String colName) {
		StringBuilder cSql=new StringBuilder("EXISTS TABLE ");
		cSql.append(this.getFormatName(tabName));
		cSql.append(" ");
		if(StrUtil.isNotEmpty(colName)){
			cSql.append(" COLUMN ");
			cSql.append(this.getFormatName(colName));
		}
		return cSql.toString();
	}

	@Override
	public String getCreateTableSql(DbTable table) {
		
		StringBuilder createSql=new StringBuilder("CREATE TABLE ");
		
		//设置表名称
		createSql.append(this.getFormatName(table.getName()));
		createSql.append(" (");
		
		//记录主键列
		List<String> primaryColumns=new ArrayList<String>();
		
		//循环处理创建列的Sql
		for (int i = 0; i < table.getColumns().size(); i++) {
			DbColumn column=table.getColumns().get(i);
			
			//判断是否是主键
			if(column.isPrimary()) {
				primaryColumns.add(this.getFormatName(column.getName()));
			}
			if(i!=0) {
				createSql.append(",");
			}
			
			//加入到建表Sql
			createSql.append(this.getCreateOrModifyColumnSql(column));
			
		}
		createSql.append(",");
		
		//创建主键
		createSql.append("PRIMARY KEY (");
		createSql.append(String.join(",", primaryColumns));
		createSql.append(")");
		createSql.append(") ");
		return createSql.toString();
	}

	@Override
	public String getCreateColumnSql(String taName, DbColumn column) {
		StringBuilder cSql=new StringBuilder(" ALTER TABLE ");
		//创建备注说明语句
		cSql.append(this.getFormatName(taName));
		cSql.append(" ADD COLUMN ");
		cSql.append(this.getCreateOrModifyColumnSql(column));
		return cSql.toString();
	}

	@Override
	public String getDeleteColumnSql(String taName, String columnName) {
		String sql="ALTER TABLE "+this.getFormatName(taName)+" DROP COLUMN "+this.getFormatName(columnName);
		return sql;
	}

	@Override
	public abstract String getPageSql(String sql, int start, int limit);

	@Override
	public String getCreateIndexSql(String tableName, DbIndex dbIndex) {
		String [] columns=dbIndex.getColumns().split(",");
		String columnSql="";
		for (String col : columns) {
			col=col.trim();
			String cols []=col.split("\\s+");
			if(columnSql.length()==0) {
				if(cols.length==1) {
					columnSql=this.getFormatName(cols[0]);
				}else {
					columnSql=this.getFormatName(cols[0])+" "+cols[1];
				}
			}else {
				if(cols.length==1) {
					columnSql+=","+this.getFormatName(cols[0]);
				}else {
					columnSql+=","+this.getFormatName(cols[0])+" "+cols[1];
				}
			}
		}
		
		String sql="CREATE INDEX "+this.getFormatName(dbIndex.getIndexName())+" ON "+this.getFormatName(tableName)+" ("+columnSql+")";
		return sql;
	}

	@Override
	public String getDeleteIndexSql(String tableName, String indexName) {
		String sql="DROP INDEX "+this.getFormatName(indexName)+" ON "+this.getFormatName(tableName);
		return sql;
	}

	@Override
	public abstract String getExistsIndexSql(String tableName, String indexName);
}
